获取本季度上季度的时间

SELECT CURRENT_DATE;

current_date() 2021-06-17 Time taken: 0.077 seconds, Fetched 1 row(s)

select date_sub(current_date,dayofmonth(current_date)-1);

date_sub(current_date(), (dayofmonth(current_date()) - 1)) 2021-06-01 Time taken: 0.076 seconds, Fetched 1 row(s)

select add_months(date_sub(current_date,dayofmonth(current_date)-1),1);

add_months(date_sub(current_date(), (dayofmonth(current_date()) - 1)), 1) 2021-07-01 Time taken: 0.05 seconds, Fetched 1 row(s)

select dayofmonth(current_date);

dayofmonth(current_date()) 17 Time taken: 0.066 seconds, Fetched 1 row(s)

select last_day(current_date);

last_day(current_date()) 2021-06-30 Time taken: 0.039 seconds, Fetched 1 row(s)

select date_sub(next_day(CURRENT_DATE,'MO'),7);

date_sub(next_day(current_date(), MO), 7) 2021-06-14 Time taken: 0.045 seconds, Fetched 1 row(s)

select date_sub(next_day(CURRENT_DATE,'MO'),14);

date_sub(next_day(current_date(), MO), 14) 2021-06-07 Time taken: 0.067 seconds, Fetched 1 row(s)

select date_sub(next_day(CURRENT_DATE,'MO'),8);

date_sub(next_day(current_date(), MO), 8) 2021-06-13 Time taken: 0.033 seconds, Fetched 1 row(s)

select date_sub(next_day(CURRENT_DATE,'MO'),6);

date_sub(next_day(current_date(), MO), 6) 2021-06-15 Time taken: 0.041 seconds, Fetched 1 row(s)

yyyy-mm-dd（获取其他周几调整最后参数）

select date_sub(next_day(CURRENT_DATE,'MO'),13) ;

date_sub(next_day(current_date(), MO), 13) 2021-06-08 Time taken: 0.059 seconds, Fetched 1 row(s)

yyyy-mm-dd（调整最后参数）

select add_months(CURRENT_DATE,-3);

add_months(current_date(), -3) 2021-03-17 Time taken: 0.04 seconds, Fetched 1 row(s)

datediff(string enddate, string startdate);

spark-sql> select datediff('2021-06-15','2021-06-20'); datediff(CAST(2021-06-15 AS DATE), CAST(2021-06-20 AS DATE)) -5 Time taken: 0.026 seconds, Fetched 1 row(s)

date_add(string startdate, int days)

select date_add('2021-06-15', 5);

date_add(CAST(2021-06-15 AS DATE), 5) 2021-06-20 Time taken: 0.058 seconds, Fetched 1 row(s)

date_sub(string startdate, int days);

select date_sub('2021-06-15', 5);

date_sub(CAST(2021-06-15 AS DATE), 5) 2021-06-10 Time taken: 0.067 seconds, Fetched 1 row(s)

select add_months(concat(year(CURRENT_DATE),'-',substr(concat('0',floor((month(CURRENT_DATE)+2)/3)*3+1),-2),'-01'),-6);

add_months(CAST(concat(CAST(year(current_date()) AS STRING), -, substring(concat(0, CAST(((FLOOR((CAST((month(current_date()) + 2) AS DOUBLE) / CAST(3 AS DOUBLE))) * CAST(3 AS BIGINT)) + CAST(1 AS BIGINT)) AS STRING)), -2, 2147483647), -01) AS DATE), -6) 2021-01-01 Time taken: 0.09 seconds, Fetched 1 row(s)

select add_months(concat(year(CURRENT_DATE),'-',substr(concat('0',floor((month(CURRENT_DATE)+2)/3)*3+1),-2),'-01'),-3);

add_months(CAST(concat(CAST(year(current_date()) AS STRING), -, substring(concat(0, CAST(((FLOOR((CAST((month(current_date()) + 2) AS DOUBLE) / CAST(3 AS DOUBLE))) * CAST(3 AS BIGINT)) + CAST(1 AS BIGINT)) AS STRING)), -2, 2147483647), -01) AS DATE), -3) 2021-04-01 Time taken: 0.058 seconds, Fetched 1 row(s)

select add_months(CURRENT_DATE,-3);

add_months(current_date(), -3) 2021-03-17 Time taken: 0.05 seconds, Fetched 1 row(s)

select add_months(concat(year(CURRENT_DATE),'-',substr(concat('0',floor((month(CURRENT_DATE)+2)/3)*3+1),-2),'-01'),-15);

add_months(CAST(concat(CAST(year(current_date()) AS STRING), -, substring(concat(0, CAST(((FLOOR((CAST((month(current_date()) + 2) AS DOUBLE) / CAST(3 AS DOUBLE))) * CAST(3 AS BIGINT)) + CAST(1 AS BIGINT)) AS STRING)), -2, 2147483647), -01) AS DATE), -15) 2020-04-01 Time taken: 0.06 seconds, Fetched 1 row(s)

select add_months(CURRENT_DATE,-12);

add_months(current_date(), -12) 2020-06-17 Time taken: 0.024 seconds, Fetched 1 row(s)